package com.alumni_circle.mapper.home;

import com.alumni_circle.entity.home.LostFound;
import com.alumni_circle.entity.home.VO.LostFoundViewVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author 龙小虬
 * @since 2020-07-06 22:11
 */
public interface LostFoundMapper extends BaseMapper<LostFound> {

    @Select("SELECT lost_found.id,lost_found.content,lost_found.phone,lost_found.goods,lost_found.place,lost_found.time,lost_found.picture,lost_found.create_time,user.`name`,user.`icon` " +
            "FROM user_lost,user,lost_found " +
            "WHERE user.id = user_lost.user_id and lost_found.state = '0' and  user_lost.lost_id = lost_found.id " +
            "ORDER BY create_time DESC")
    List<LostFoundViewVO> selectListLostFound();


    /**
     * 每个用户自己发布的内容
     * @param id
     * @return
     */
    @Select("SELECT lost_found.content,lost_found.phone,lost_found.id,lost_found.goods,lost_found.place,lost_found.time,lost_found.picture,lost_found.create_time,user.`name`,user.`icon` " +
            "FROM user_lost,user,lost_found " +
            "WHERE user.id = user_lost.user_id and lost_found.state = '0' and user_lost.lost_id = lost_found.id and user.id = #{id} " +
            "ORDER BY create_time DESC")
    List<LostFoundViewVO> selectMyListLostFound(@Param("id") String id);

    /**
     * 搜索后的内容
     * @param search 搜索条件
     * @return
     */
    @Select("SELECT lost_found.phone,lost_found.id,lost_found.goods,lost_found.place,lost_found.time,lost_found.picture,lost_found.create_time,lost_found.content,user.`name`,user.`icon` \n" +
            "FROM user_lost,user,lost_found \n" +
            "WHERE (user.id = user_lost.user_id and lost_found.state = '0' and user_lost.lost_id = lost_found.id) and (lost_found.content like concat('%',#{search},'%') or lost_found.place like concat('%',#{search},'%') or lost_found.goods like concat('%',#{search},'%'))\n" +
            "ORDER BY create_time DESC")
    List<LostFoundViewVO> searchListLostFound(@Param("search") String search);


    @Select("SELECT lost_found.phone,lost_found.id,lost_found.goods,lost_found.place,lost_found.time,lost_found.picture,lost_found.create_time,lost_found.content,user.`name`,user.`icon` \n" +
            "FROM user_lost,user,lost_found \n" +
            "WHERE (user.id = user_lost.user_id and lost_found.state = '0' and user_lost.lost_id = lost_found.id and user.id = #{id}) and (lost_found.content like concat('%',#{search},'%') or lost_found.place like concat('%',#{search},'%') or lost_found.goods like concat('%',#{search},'%'))\n" +
            "ORDER BY create_time DESC")
    List<LostFoundViewVO> searchMyListLostFound(@Param("id") String id, @Param("search") String search);
}
